<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="robots" content="noodp" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1">
        <title>mysql - 肉蛋葱鸡肉粥🍛的博客☕</title><meta name="Description" content=""><meta property="og:title" content="mysql" />
<meta property="og:description" content="概念 DB database数据库:保存一系列有组织的数据 DBMS Darabase Management System数据库管理系统。数据库是通过DBMS创建和操作的容器 SQL Structure Query Lanngu" />
<meta property="og:type" content="article" />
<meta property="og:url" content="http://example.org/posts/mysql/" /><meta property="article:section" content="posts" />
<meta property="article:published_time" content="2021-06-24T10:31:53+08:00" />
<meta property="article:modified_time" content="2021-06-24T10:31:53+08:00" /><meta property="og:site_name" content="肉蛋葱鸡肉粥🍛的博客☕" />

<meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="mysql"/>
<meta name="twitter:description" content="概念 DB database数据库:保存一系列有组织的数据 DBMS Darabase Management System数据库管理系统。数据库是通过DBMS创建和操作的容器 SQL Structure Query Lanngu"/>
<meta name="application-name" content="肉蛋葱鸡肉粥🍛的博客☕">
<meta name="apple-mobile-web-app-title" content="肉蛋葱鸡肉粥🍛的博客☕"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" />
        <link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
        <link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png"><link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png"><link rel="manifest" href="/site.webmanifest"><link rel="canonical" href="http://example.org/posts/mysql/" /><link rel="prev" href="http://example.org/posts/java/" /><link rel="next" href="http://example.org/posts/springmvc/" /><link rel="stylesheet" href="/lib/normalize/normalize.min.css"><link rel="stylesheet" href="/css/style.min.css"><link rel="stylesheet" href="/lib/fontawesome-free/all.min.css"><link rel="stylesheet" href="/lib/animate/animate.min.css"><script type="application/ld+json">
    {
        "@context": "http://schema.org",
        "@type": "BlogPosting",
        "headline": "mysql",
        "inLanguage": "zh-CN",
        "mainEntityOfPage": {
            "@type": "WebPage",
            "@id": "http:\/\/example.org\/posts\/mysql\/"
        },"genre": "posts","keywords": "mysql, 数据库","wordcount":  4123 ,
        "url": "http:\/\/example.org\/posts\/mysql\/","datePublished": "2021-06-24T10:31:53+08:00","dateModified": "2021-06-24T10:31:53+08:00","publisher": {
            "@type": "Organization",
            "name": "yzuxqz"},"author": {
                "@type": "Person",
                "name": "yzuxqz"
            },"description": ""
    }
    </script></head>
    <body header-desktop="" header-mobile=""><script type="text/javascript">(window.localStorage && localStorage.getItem('theme') ? localStorage.getItem('theme') === 'dark' : ('' === 'auto' ? window.matchMedia('(prefers-color-scheme: dark)').matches : '' === 'dark')) && document.body.setAttribute('theme', 'dark');</script>

        <div id="mask"></div><div class="wrapper"><header class="desktop" id="header-desktop">
    <div class="header-wrapper">
        <div class="header-title">
            <a href="/" title="肉蛋葱鸡肉粥🍛的博客☕">肉蛋葱鸡肉粥🍛的博客☕</a>
        </div>
        <div class="menu">
            <div class="menu-inner"><a class="menu-item" href="/posts/"> 文章 </a><a class="menu-item" href="/tags/"> 标签 </a><a class="menu-item" href="/categories/"> 分类 </a><a class="menu-item" href="/about/"> 关于 </a><span class="menu-item delimiter"></span><a href="javascript:void(0);" class="menu-item language" title="选择语言">简体中文<i class="fas fa-chevron-right fa-fw"></i>
                        <select class="language-select" id="language-select-desktop" onchange="location = this.value;"><option value="/posts/mysql/" selected>简体中文</option></select>
                    </a><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                    <i class="fas fa-adjust fa-fw"></i>
                </a>
            </div>
        </div>
    </div>
</header><header class="mobile" id="header-mobile">
    <div class="header-container">
        <div class="header-wrapper">
            <div class="header-title">
                <a href="/" title="肉蛋葱鸡肉粥🍛的博客☕">肉蛋葱鸡肉粥🍛的博客☕</a>
            </div>
            <div class="menu-toggle" id="menu-toggle-mobile">
                <span></span><span></span><span></span>
            </div>
        </div>
        <div class="menu" id="menu-mobile"><a class="menu-item" href="/posts/" title="">文章</a><a class="menu-item" href="/tags/" title="">标签</a><a class="menu-item" href="/categories/" title="">分类</a><a class="menu-item" href="/about/" title="">关于</a><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                <i class="fas fa-adjust fa-fw"></i>
            </a><a href="javascript:void(0);" class="menu-item" title="选择语言">简体中文<i class="fas fa-chevron-right fa-fw"></i>
                    <select class="language-select" onchange="location = this.value;"><option value="/posts/mysql/" selected>简体中文</option></select>
                </a></div>
    </div>
</header>
<div class="search-dropdown desktop">
    <div id="search-dropdown-desktop"></div>
</div>
<div class="search-dropdown mobile">
    <div id="search-dropdown-mobile"></div>
</div>
<main class="main">
                <div class="container"><div class="toc" id="toc-auto">
            <h2 class="toc-title">目录</h2>
            <div class="toc-content" id="toc-content-auto"></div>
        </div><article class="page single"><h1 class="single-title animated flipInX">mysql</h1><div class="post-meta">
            <div class="post-meta-line"><span class="post-author"><a href="https://github.com/yzuxqz" title="Author" target="_blank" rel="noopener noreffer author" class="author"><i class="fas fa-user-circle fa-fw"></i>yzuxqz</a></span>&nbsp;<span class="post-category">收录于 <a href="/categories/mysql/"><i class="far fa-folder fa-fw"></i>mysql</a>&nbsp;<a href="/categories/%E5%90%8E%E7%AB%AF/"><i class="far fa-folder fa-fw"></i>后端</a></span></div>
            <div class="post-meta-line"><i class="far fa-calendar-alt fa-fw"></i>&nbsp;<time datetime="2021-06-24">2021-06-24</time>&nbsp;<i class="fas fa-pencil-alt fa-fw"></i>&nbsp;约 4123 字&nbsp;
                <i class="far fa-clock fa-fw"></i>&nbsp;预计阅读 9 分钟&nbsp;</div>
        </div><div class="details toc" id="toc-static"  kept="">
                <div class="details-summary toc-title">
                    <span>目录</span>
                    <span><i class="details-icon fas fa-angle-right"></i></span>
                </div>
                <div class="details-content toc-content" id="toc-content-static"><nav id="TableOfContents">
  <ul>
    <li><a href="#db">DB</a></li>
    <li><a href="#dbms">DBMS</a></li>
    <li><a href="#sql">SQL</a></li>
  </ul>

  <ul>
    <li>
      <ul>
        <li><a href="#基础查询">基础查询</a>
          <ul>
            <li><a href="#查询表中的单个字段">查询表中的单个字段</a></li>
            <li><a href="#查询表中的多个字段">查询表中的多个字段</a></li>
            <li><a href="#查询表中的所有字段">查询表中的所有字段</a></li>
            <li><a href="#查询常量">查询常量</a></li>
            <li><a href="#查询表达式">查询表达式</a></li>
            <li><a href="#查询函数">查询函数</a></li>
            <li><a href="#起别名">起别名</a></li>
            <li><a href="#去重">去重</a></li>
            <li><a href="#号的作用">+号的作用</a></li>
            <li><a href="#concat">CONCAT</a></li>
            <li><a href="#ifnull">IFNULL</a></li>
          </ul>
        </li>
        <li><a href="#条件查询">条件查询</a>
          <ul>
            <li><a href="#分类">分类</a></li>
            <li><a href="#按条件表达式筛选">按条件表达式筛选</a></li>
            <li><a href="#按逻辑表达式筛选">按逻辑表达式筛选</a></li>
          </ul>
        </li>
        <li><a href="#模糊查询">模糊查询</a>
          <ul>
            <li><a href="#like">like</a></li>
            <li><a href="#between-and">between and</a></li>
            <li><a href="#in">in</a></li>
            <li><a href="#is-null">is null</a></li>
            <li><a href="#安全等于">安全等于</a></li>
          </ul>
        </li>
        <li><a href="#排序查询">排序查询</a>
          <ul>
            <li><a href="#按表达式排序">按表达式排序</a></li>
            <li><a href="#按别名排序">按别名排序</a></li>
            <li><a href="#按函数排序">按函数排序</a></li>
            <li><a href="#排序多个字段">排序多个字段</a></li>
          </ul>
        </li>
      </ul>
    </li>
  </ul>

  <ul>
    <li><a href="#单行函数">单行函数</a>
      <ul>
        <li><a href="#字符函数">字符函数</a></li>
        <li><a href="#数学函数">数学函数</a></li>
        <li><a href="#日期函数">日期函数</a></li>
        <li><a href="#其他函数">其他函数</a></li>
        <li><a href="#流程控制函数">流程控制函数</a></li>
      </ul>
    </li>
    <li><a href="#分组函数">分组函数</a></li>
  </ul>

  <ul>
    <li><a href="#库的管理">库的管理</a>
      <ul>
        <li><a href="#创建">创建</a></li>
        <li><a href="#修改">修改</a></li>
        <li><a href="#删除">删除</a></li>
      </ul>
    </li>
    <li><a href="#表的管理">表的管理</a>
      <ul>
        <li><a href="#创建-1">创建</a></li>
        <li><a href="#修改删除">修改&amp;删除</a></li>
        <li><a href="#复制">复制</a></li>
      </ul>
    </li>
  </ul>

  <ul>
    <li><a href="#整型">整型</a>
      <ul>
        <li><a href="#浮点型">浮点型</a></li>
      </ul>
    </li>
    <li><a href="#字符型">字符型</a></li>
    <li><a href="#日期型">日期型</a></li>
  </ul>

  <ul>
    <li><a href="#列级约束">列级约束</a></li>
    <li><a href="#表级约束">表级约束</a></li>
    <li><a href="#外键的特点">外键的特点</a></li>
  </ul>

  <ul>
    <li><a href="#事务">事务</a></li>
    <li><a href="#存储引擎">存储引擎</a></li>
    <li><a href="#事务的特性">事务的特性</a></li>
    <li><a href="#使用步骤">使用步骤</a></li>
    <li><a href="#事务隔离">事务隔离</a></li>
    <li><a href="#设置隔离级别">设置隔离级别</a></li>
    <li><a href="#savepoint设置回滚点">savepoint设置回滚点</a></li>
  </ul>
</nav></div>
            </div><div class="content" id="content"><h1 id="概念">概念</h1>
<h2 id="db">DB</h2>
<p>database数据库:保存一系列有组织的数据</p>
<h2 id="dbms">DBMS</h2>
<p>Darabase Management System数据库管理系统。数据库是通过DBMS创建和操作的容器</p>
<h2 id="sql">SQL</h2>
<p>Structure Query Lannguage结构化查询语句：用来和数据库通信的语言</p>
<h1 id="特点">特点</h1>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%89%B9%E7%82%B9.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%89%B9%E7%82%B9.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%89%B9%E7%82%B9.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%89%B9%E7%82%B9.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%89%B9%E7%82%B9.png"
        title="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%89%B9%E7%82%B9.png" /></p>
<h1 id="启动">启动</h1>
<ol>
<li>右击计算机，管理，服务</li>
</ol>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%90%AF%E5%8A%A8.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%90%AF%E5%8A%A8.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%90%AF%E5%8A%A8.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%90%AF%E5%8A%A8.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%90%AF%E5%8A%A8.png"
        title="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%90%AF%E5%8A%A8.png" /></p>
<h1 id="登录">登录</h1>
<p>mysql -h 主机名 -P 端口号（默认3306） -u 用户 -p密码</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%99%BB%E5%BD%95.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%99%BB%E5%BD%95.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%99%BB%E5%BD%95.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%99%BB%E5%BD%95.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%99%BB%E5%BD%95.png"
        title="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%99%BB%E5%BD%95.png" /></p>
<p>本机登录简写：</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%AC%E6%9C%BA%E7%99%BB%E5%BD%95.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%AC%E6%9C%BA%E7%99%BB%E5%BD%95.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%AC%E6%9C%BA%E7%99%BB%E5%BD%95.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%AC%E6%9C%BA%E7%99%BB%E5%BD%95.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%AC%E6%9C%BA%E7%99%BB%E5%BD%95.png"
        title="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%AC%E6%9C%BA%E7%99%BB%E5%BD%95.png" /></p>
<h1 id="常见命令">常见命令</h1>
<p>登录数据库后使用</p>
<table>
<thead>
<tr>
<th></th>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td>show databases；</td>
<td>显示数据库中的表</td>
</tr>
<tr>
<td>use 库名；</td>
<td>进入库</td>
</tr>
<tr>
<td>show tables；</td>
<td>显示进入的库中有哪些表</td>
</tr>
<tr>
<td>show tables from 库名；</td>
<td>直接显示库中的表，但是并没有进入库</td>
</tr>
<tr>
<td>select database()；</td>
<td>查看当前在那个库中</td>
</tr>
<tr>
<td>desc 表名；</td>
<td>查看表结构</td>
</tr>
<tr>
<td>create tables 表名(  列名 列类型，列名 列类型)</td>
<td>创建表</td>
</tr>
<tr>
<td>select version()；</td>
<td>查看服务器版本</td>
</tr>
</tbody>
</table>
<h1 id="语法规范">语法规范</h1>
<ol>
<li>
<p>不区分大小写，但是建议关键字大写，表名，列名小写</p>
</li>
<li>
<p>每条命令分号结尾</p>
</li>
<li>
<p>可以缩进换行（不写分号回车就是换行）</p>
</li>
<li>
<p>注释</p>
<p>​	单行注释：#注释文字 或者 &ndash; 注释文字</p>
<p>​	多行注释：/ <em>注释文字</em>  /</p>
</li>
</ol>
<h1 id="dql">DQL</h1>
<p>database query language</p>
<h3 id="基础查询">基础查询</h3>
<ol>
<li>
<p>select 查询列表 from 表名；</p>
<p>==特点==：</p>
<ol>
<li>查询列表可以是：表中的字段，常量值，表达式，函数，查询的结果可以是一个虚拟的表格</li>
</ol>
</li>
</ol>
<h4 id="查询表中的单个字段">查询表中的单个字段</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="err">字段名</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="err">表名</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="查询表中的多个字段">查询表中的多个字段</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="err">字段名</span><span class="mi">1</span><span class="p">,</span><span class="err">字段名</span><span class="mi">2</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="err">表名</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="查询表中的所有字段">查询表中的所有字段</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="err">表名</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><p>==注意==：</p>
<ol>
<li>在查询前 use 库名；</li>
<li>反单引号用来区分关键字和字段</li>
</ol>
<h4 id="查询常量">查询常量</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="mi">100</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="s2">&#34;john&#34;</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="查询表达式">查询表达式</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="mi">100</span><span class="o">*</span><span class="mi">98</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="查询函数">查询函数</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="nf">VERSION</span><span class="p">();</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="起别名">起别名</h4>
<ol>
<li>便于理解</li>
<li>如果查询的字段有重名的情况，用别名可以区分开</li>
</ol>
<p>使用AS:</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="w"> </span><span class="o">==</span><span class="k">AS</span><span class="o">==</span><span class="w"> </span><span class="err">姓</span><span class="p">,</span><span class="n">first_name</span><span class="w"> </span><span class="o">==</span><span class="k">AS</span><span class="o">==</span><span class="w"> </span><span class="err">名</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><p>使用空格：</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="w"> </span><span class="err">姓</span><span class="p">,</span><span class="n">first_name</span><span class="w"> </span><span class="err">名</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><p>==注意==：</p>
<ol>
<li>如果别名中有关键字，要加双引号</li>
</ol>
<h4 id="去重">去重</h4>
<p>关键字 DISTINCT</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="k">DISTINCT</span><span class="w"> </span><span class="n">department_id</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="号的作用">+号的作用</h4>
<ol>
<li>
<p>只是运算符</p>
</li>
<li>
<p>在运算时会将字符型转为数值型</p>
<p>如果转换成功，则继续做加法</p>
<p>如果转换失败，则字符型转为0，再做加法</p>
</li>
<li>
<p>只要有null，结果为null</p>
</li>
</ol>
<h4 id="concat">CONCAT</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w">	</span><span class="nf">CONCAT</span><span class="p">(</span><span class="err">字段名</span><span class="mi">1</span><span class="p">,</span><span class="err">字段名</span><span class="mi">2</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">姓名</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="ifnull">IFNULL</h4>
<p>参数1：原来的字段名</p>
<p>参数2：如果字段值为null，修改为</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="k">SELECT</span><span class="w">	</span><span class="n">IFNULL</span><span class="p">(</span><span class="n">commission_pct</span><span class="p">,</span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">奖金率</span><span class="p">,</span><span class="n">commission_pct</span><span class="w">
</span><span class="w"></span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h3 id="条件查询">条件查询</h3>
<ol>
<li>select 查询列表 from 表名 where 筛选条件</li>
</ol>
<h4 id="分类">分类</h4>
<ol>
<li>
<p>按条件表达式筛选</p>
<p>条件运算符：&gt; &lt; = != &lt;&gt; &gt;= &lt;=</p>
</li>
<li>
<p>按逻辑表达式筛选</p>
<p>逻辑运算符：&amp;&amp; || ！ and or not</p>
</li>
<li>
<p>模糊查询</p>
<p>like</p>
<p>between and</p>
<p>in</p>
<p>isnull</p>
</li>
</ol>
<h4 id="按条件表达式筛选">按条件表达式筛选</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">salary</span><span class="o">&gt;</span><span class="mi">12000</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="p">,</span><span class="o">`</span><span class="n">department_id</span><span class="o">`</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="o">`</span><span class="n">department_id</span><span class="o">`&lt;&gt;</span><span class="mi">90</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="按逻辑表达式筛选">按逻辑表达式筛选</h4>
<p>作用：用于连接条件表达式</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="p">,</span><span class="n">salary</span><span class="p">,</span><span class="n">commission_pct</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">salary</span><span class="o">&gt;=</span><span class="mi">10000</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="n">salary</span><span class="o">&lt;=</span><span class="mi">20000</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="p">,</span><span class="n">salary</span><span class="p">,</span><span class="n">commission_pct</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="o">`</span><span class="n">department_id</span><span class="o">`&lt;</span><span class="mi">90</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="o">`</span><span class="n">department_id</span><span class="o">`&gt;</span><span class="mi">110</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="n">salary</span><span class="o">&gt;</span><span class="mi">15000</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="o">//</span><span class="err">相当于</span><span class="w">
</span><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="p">,</span><span class="n">salary</span><span class="p">,</span><span class="n">commission_pct</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="k">NOT</span><span class="p">(</span><span class="o">`</span><span class="n">department_id</span><span class="o">`&gt;=</span><span class="mi">90</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="o">`</span><span class="n">department_id</span><span class="o">`&lt;=</span><span class="mi">110</span><span class="p">)</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="n">salary</span><span class="o">&gt;</span><span class="mi">15000</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h3 id="模糊查询">模糊查询</h3>
<h4 id="like">like</h4>
<p>一般和通配符搭配式使用，通配符：</p>
<ol>
<li>% ：任意多个字符，包含0个字符</li>
<li>_：任意单个字符，一个下划线表示一个字符</li>
</ol>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="c1">#员工名字包含字符a
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">last_name</span><span class="w"> </span><span class="k">LIKE</span><span class="w"> </span><span class="s1">&#39;%a%&#39;</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="c1">#员工名中第三个字符为a，第五个字符为a
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="p">,</span><span class="n">salary</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">last_name</span><span class="w"> </span><span class="k">LIKE</span><span class="w"> </span><span class="s1">&#39;__e_a%&#39;</span><span class="w">
</span><span class="w"></span><span class="c1">#员工名中第二个字符为下划线的
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">last_name</span><span class="w"> </span><span class="k">LIKE</span><span class="w"> </span><span class="s1">&#39;_\_%&#39;</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><p>==注意==：</p>
<ol>
<li>如果查询的字符为下划线，要转义下划线</li>
</ol>
<h4 id="between-and">between and</h4>
<ol>
<li>提高语句的简洁度</li>
<li>包含临界值</li>
<li>两个临界值不能换顺序</li>
</ol>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="c1">#员工编号100-120
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="o">`</span><span class="n">employee_id</span><span class="o">`&gt;=</span><span class="mi">100</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="o">`</span><span class="n">employee_id</span><span class="o">`&lt;=</span><span class="mi">120</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="o">`</span><span class="n">employee_id</span><span class="o">`</span><span class="w"> </span><span class="k">BETWEEN</span><span class="w"> </span><span class="mi">100</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="mi">120</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="in">in</h4>
<ol>
<li>用于判断某字段的值是否属于in列表中的某一项</li>
<li>特点：
<ol>
<li>使用in比使用or提高了语句简洁度</li>
<li>in列表的值类型必须统一或兼容</li>
<li>不能使用通配符</li>
</ol>
</li>
</ol>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="c1">#工种编号是 IT_	PROG，AD_VP其中一个的员工信息
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="p">,</span><span class="n">job_id</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">job_id</span><span class="w"> </span><span class="k">IN</span><span class="p">(</span><span class="s1">&#39;IT_PROT&#39;</span><span class="p">,</span><span class="s1">&#39;AD_VP&#39;</span><span class="p">);</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="is-null">is null</h4>
<p>只能判断null值</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="c1">#没有奖金的员工
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="p">,</span><span class="n">commission_pct</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">commission_pct</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="no">NULL</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="c1">#有将金
</span><span class="c1"></span><span class="k">IS</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="no">NULL</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="安全等于">安全等于</h4>
<p>&lt;=&gt;</p>
<p>不仅仅能查null值，包括其他的值</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="c1">#没有奖金的员工
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="p">,</span><span class="n">commission_pct</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">commission_pct</span><span class="w"> </span><span class="o">&lt;=&gt;</span><span class="no">NULL</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h3 id="排序查询">排序查询</h3>
<ol>
<li>order by可以支持单个字段，多个字段，表达式，函数，别名</li>
<li>asc代表升序，desc代表降序，如果不写，默认是升序</li>
<li>order by子句一般是放在查询语句的最后面，limit子句除外</li>
</ol>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="c1">#排序查询
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">salary</span><span class="w"> </span><span class="k">DESC</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="c1">#DESC是从高到低
</span><span class="c1">#ASC是从低到高，如果不写默认是升序
</span></code></pre></td></tr></table>
</div>
</div><h4 id="按表达式排序">按表达式排序</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="p">,</span><span class="n">salary</span><span class="o">*</span><span class="mi">12</span><span class="o">*</span><span class="p">(</span><span class="mi">1</span><span class="o">+</span><span class="nf">IFNULL</span><span class="p">(</span><span class="n">commission_pct</span><span class="p">,</span><span class="mi">0</span><span class="p">))</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">年薪</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">salary</span><span class="o">*</span><span class="mi">12</span><span class="o">*</span><span class="p">(</span><span class="mi">1</span><span class="o">+</span><span class="nf">IFNULL</span><span class="p">(</span><span class="n">commission_pct</span><span class="p">,</span><span class="mi">0</span><span class="p">))</span><span class="w"> </span><span class="k">DESC</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="按别名排序">按别名排序</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="p">,</span><span class="n">salary</span><span class="o">*</span><span class="mi">12</span><span class="o">*</span><span class="p">(</span><span class="mi">1</span><span class="o">+</span><span class="nf">IFNULL</span><span class="p">(</span><span class="n">commission_pct</span><span class="p">,</span><span class="mi">0</span><span class="p">))</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">年薪</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="err">年薪</span><span class="w"> </span><span class="k">DESC</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="按函数排序">按函数排序</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="c1">#根据姓名字节长度进行排序
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">LENGTH</span><span class="p">(</span><span class="o">`</span><span class="n">last_name</span><span class="o">`</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">字节长度</span><span class="p">,</span><span class="o">`</span><span class="n">last_name</span><span class="o">`</span><span class="p">,</span><span class="o">`</span><span class="n">salary</span><span class="o">`</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="nf">LENGTH</span><span class="p">(</span><span class="n">last_name</span><span class="p">)</span><span class="w"> </span><span class="k">DESC</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h4 id="排序多个字段">排序多个字段</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="c1">#多个字段排序
</span><span class="c1"></span><span class="k">SELECT</span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">salary</span><span class="w"> </span><span class="k">ASC</span><span class="p">,</span><span class="o">`</span><span class="n">employee_id</span><span class="o">`</span><span class="w"> </span><span class="k">DESC</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h1 id="dml">DML</h1>
<h1 id="mysql函数">MySQL函数</h1>
<h2 id="单行函数">单行函数</h2>
<h3 id="字符函数">字符函数</h3>
<table>
<thead>
<tr>
<th></th>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td>length(字段名)</td>
<td>获取字段的长度</td>
</tr>
<tr>
<td>concat(字段名1，字段名2)</td>
<td>连接字段</td>
</tr>
<tr>
<td>upper(字段名)</td>
<td>大写</td>
</tr>
<tr>
<td>lower(字段名)</td>
<td>小写</td>
</tr>
<tr>
<td>substr(字段名，索引)</td>
<td>截取</td>
</tr>
<tr>
<td>instr（字段名，子串字段）</td>
<td>返回子串第一次出现的索引</td>
</tr>
<tr>
<td>trim（x from 字段名）</td>
<td>去除首尾字符</td>
</tr>
<tr>
<td>lpad（字段，指定长度，指定字符）</td>
<td>用指定字符填充指定长度</td>
</tr>
<tr>
<td>replace（字段，要替换的，替换为）</td>
<td></td>
</tr>
<tr>
<td>ifnull(字段名，若为空替换为)</td>
<td>判断为空，替换为</td>
</tr>
</tbody>
</table>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="c1">#字符函数
</span><span class="c1">#upper,lower
</span><span class="c1"></span><span class="k">SELECT</span><span class="w">	</span><span class="nf">CONCAT</span><span class="p">(</span><span class="nf">UPPER</span><span class="p">(</span><span class="n">last_name</span><span class="p">),</span><span class="nf">LOWER</span><span class="p">(</span><span class="n">first_name</span><span class="p">))</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">姓名</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="c1">#substr,substring
</span><span class="c1"></span><span class="err">注意：索引从</span><span class="mi">1</span><span class="err">开始</span><span class="w">
</span><span class="w"></span><span class="c1">#截取索引处后面所有字符
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">SUBSTR</span><span class="p">(</span><span class="s1">&#39;李莫愁爱上了陆展元&#39;</span><span class="p">,</span><span class="mi">7</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">out_put</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="c1">#截取指定长度的字符
</span><span class="c1"></span><span class="k">SELECT</span><span class="w">	</span><span class="nf">SUBSTR</span><span class="p">(</span><span class="s1">&#39;李莫愁爱上了陆展元&#39;</span><span class="p">,</span><span class="mi">1</span><span class="p">,</span><span class="mi">3</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">out_put</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="c1">#姓名首字母大写，其他字符小写，然后_拼接
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">CONCAT</span><span class="p">(</span><span class="nf">UPPER</span><span class="p">(</span><span class="nf">SUBSTR</span><span class="p">(</span><span class="o">`</span><span class="n">last_name</span><span class="o">`</span><span class="p">,</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)),</span><span class="s1">&#39;_&#39;</span><span class="p">,</span><span class="nf">LOWER</span><span class="p">(</span><span class="nf">SUBSTR</span><span class="p">(</span><span class="o">`</span><span class="n">last_name</span><span class="o">`</span><span class="p">,</span><span class="mi">2</span><span class="p">)))</span><span class="w">	 </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="c1">#instr 返回子串第一次出现的索引，如果找不到返回0
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">INSTR</span><span class="p">(</span><span class="s1">&#39;杨不悔爱上了殷六侠&#39;</span><span class="p">,</span><span class="s1">&#39;殷六侠&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">out_put</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="c1">#trim 去除首位字符
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">LENGTH</span><span class="p">(</span><span class="nf">TRIM</span><span class="p">(</span><span class="s1">&#39;    张翠删      &#39;</span><span class="p">))</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">out_put</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">TRIM</span><span class="p">(</span><span class="s1">&#39;a&#39;</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="s1">&#39;aaaaaaa张aaaaaaaaa翠山aaaaaaaaaaaaaa&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">out_put</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="c1">#lpad 用指定的字符实现左填充指定长度
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">LPAD</span><span class="p">(</span><span class="s1">&#39;殷素素&#39;</span><span class="p">,</span><span class="mi">10</span><span class="p">,</span><span class="s1">&#39;*&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">out_put</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="c1">#rpad
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">RPAD</span><span class="p">(</span><span class="s1">&#39;殷素素&#39;</span><span class="p">,</span><span class="mi">10</span><span class="p">,</span><span class="s1">&#39;*&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">out_put</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="c1">#replace 替换
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="k">REPLACE</span><span class="p">(</span><span class="s1">&#39;aaabbbccc&#39;</span><span class="p">,</span><span class="s1">&#39;a&#39;</span><span class="p">,</span><span class="s1">&#39;d&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">out_put</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h3 id="数学函数">数学函数</h3>
<table>
<thead>
<tr>
<th></th>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td>round</td>
<td>四舍五入</td>
</tr>
<tr>
<td>cell</td>
<td>向上取整</td>
</tr>
<tr>
<td>floor</td>
<td>向下取整</td>
</tr>
<tr>
<td>truncate（1.65，1）</td>
<td>截断，小数点后保留多少位</td>
</tr>
<tr>
<td>MOD(字段1，字段2)</td>
<td>取余</td>
</tr>
</tbody>
</table>
<h3 id="日期函数">日期函数</h3>
<table>
<thead>
<tr>
<th></th>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td>now</td>
<td>当前日期和时间</td>
</tr>
<tr>
<td>curdate</td>
<td>日期</td>
</tr>
<tr>
<td>curtime</td>
<td>时间</td>
</tr>
<tr>
<td>year</td>
<td>年</td>
</tr>
<tr>
<td>month</td>
<td>月</td>
</tr>
<tr>
<td>monthname</td>
<td>月的英文</td>
</tr>
<tr>
<td>str_to_date</td>
<td>字符转为指定格式日期</td>
</tr>
<tr>
<td>date_format</td>
<td>日期抓为字符</td>
</tr>
</tbody>
</table>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0.png"
        title="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0.png" /></p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0%E6%A0%BC%E5%BC%8F%E7%AC%A6.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0%E6%A0%BC%E5%BC%8F%E7%AC%A6.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0%E6%A0%BC%E5%BC%8F%E7%AC%A6.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0%E6%A0%BC%E5%BC%8F%E7%AC%A6.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0%E6%A0%BC%E5%BC%8F%E7%AC%A6.png"
        title="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0%E6%A0%BC%E5%BC%8F%E7%AC%A6.png" /></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-mysql" data-lang="mysql"><span class="c1">#日期函数
</span><span class="c1">#日期加时间
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">NOW</span><span class="p">();</span><span class="w">
</span><span class="w"></span><span class="c1">#日期
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">CURDATE</span><span class="p">();</span><span class="w">
</span><span class="w"></span><span class="c1">#时间
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">CURTIME</span><span class="p">();</span><span class="w">
</span><span class="w"></span><span class="c1">#获取指定的部分，年，月，日，小时，分钟，秒
</span><span class="c1"></span><span class="k">SELECT</span><span class="w">	</span><span class="kt">YEAR</span><span class="p">(</span><span class="nf">NOW</span><span class="p">())</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">年</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="kt">YEAR</span><span class="p">(</span><span class="s1">&#39;1998-1-1&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">年</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">MONTH</span><span class="p">(</span><span class="nf">NOW</span><span class="p">())</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">月</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">MONTHNAME</span><span class="p">(</span><span class="nf">NOW</span><span class="p">())</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">月</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="c1">#str_to_date 将字符通过指定的格式转换成日期
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="nf">STR_TO_DATE</span><span class="p">(</span><span class="s1">&#39;1999-3-2&#39;</span><span class="p">,</span><span class="s1">&#39;%Y-%c-%d&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">out_put</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="c1">#查询入职日期为1992-4-3的员工信息
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">hiredate</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;1992-4-3&#39;</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">hiredate</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="nf">STR_TO_DATE</span><span class="p">(</span><span class="s1">&#39;4-3 1992&#39;</span><span class="p">,</span><span class="s1">&#39;%c-%d %Y&#39;</span><span class="p">);</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="c1">#date_format 将日期转换成字符
</span><span class="c1"></span><span class="k">SELECT</span><span class="w">	</span><span class="nf">DATE_FORMAT</span><span class="p">(</span><span class="nf">NOW</span><span class="p">(),</span><span class="s1">&#39;%y年%m月%d日&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">out_put</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="c1">#查询有将金的员工名和入职日期
</span><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">last_name</span><span class="p">,</span><span class="nf">DATE_FORMAT</span><span class="p">(</span><span class="n">hiredate</span><span class="p">,</span><span class="s1">&#39;%m月%d日 %y年&#39;</span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="err">入职日期</span><span class="w">
</span><span class="w"></span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="w">
</span><span class="w"></span><span class="k">WHERE</span><span class="w"> </span><span class="n">commission_pct</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="no">NULL</span><span class="p">;</span><span class="w">
</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><h3 id="其他函数">其他函数</h3>
<h3 id="流程控制函数">流程控制函数</h3>
<h2 id="分组函数">分组函数</h2>
<table>
<thead>
<tr>
<th></th>
<th></th>
</tr>
</thead>
<tbody>
<tr>
<td></td>
<td></td>
</tr>
<tr>
<td></td>
<td></td>
</tr>
<tr>
<td></td>
<td></td>
</tr>
</tbody>
</table>
<h1 id="ddl">DDL</h1>
<h2 id="库的管理">库的管理</h2>
<h3 id="创建">创建</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">create database 库名;
</code></pre></td></tr></table>
</div>
</div><h3 id="修改">修改</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">RENAME DATABASE books TO 新库名;
</code></pre></td></tr></table>
</div>
</div><h3 id="删除">删除</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">DROP DATABASE IF EXISTS 库名;
</code></pre></td></tr></table>
</div>
</div><h2 id="表的管理">表的管理</h2>
<h3 id="创建-1">创建</h3>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216152539276.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216152539276.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216152539276.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216152539276.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216152539276.png"
        title="image-20210216152539276" /></p>
<h3 id="修改删除">修改&amp;删除</h3>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216152929451.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216152929451.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216152929451.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216152929451.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216152929451.png"
        title="image-20210216152929451" /></p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216153138919.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216153138919.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216153138919.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216153138919.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216153138919.png"
        title="image-20210216153138919" /></p>
<h3 id="复制">复制</h3>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216153657101.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216153657101.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216153657101.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216153657101.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216153657101.png"
        title="image-20210216153657101" /></p>
<h1 id="数据类型">数据类型</h1>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216154808087.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216154808087.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216154808087.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216154808087.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216154808087.png"
        title="image-20210216154808087" /></p>
<h2 id="整型">整型</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216154838630.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216154838630.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216154838630.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216154838630.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216154838630.png"
        title="image-20210216154838630" /></p>
<p>设置无符号</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155042927.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155042927.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155042927.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155042927.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155042927.png"
        title="image-20210216155042927" /></p>
<h3 id="浮点型">浮点型</h3>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155545561.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155545561.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155545561.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155545561.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155545561.png"
        title="image-20210216155545561" /></p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155808617.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155808617.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155808617.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155808617.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216155808617.png"
        title="image-20210216155808617" /></p>
<p>注意：如果是decimal，则M默认为10，D默认为0，如果是float或者double根据字符插入的精度来改变精度</p>
<h2 id="字符型">字符型</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160033352.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160033352.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160033352.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160033352.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160033352.png"
        title="image-20210216160033352" /></p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160115160.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160115160.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160115160.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160115160.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160115160.png"
        title="image-20210216160115160" /></p>
<p>区别：char不可变，varchar可变。char更耗费空间，但是char效率更高。所有固定时用char，有变化的用varchar</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160408391.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160408391.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160408391.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160408391.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160408391.png"
        title="image-20210216160408391" /></p>
<h2 id="日期型">日期型</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160503151.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160503151.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160503151.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160503151.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160503151.png"
        title="image-20210216160503151" /></p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160720993.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160720993.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160720993.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160720993.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216160720993.png"
        title="image-20210216160720993" /></p>
<h1 id="约束">约束</h1>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161107116.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161107116.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161107116.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161107116.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161107116.png"
        title="image-20210216161107116" /></p>
<h2 id="列级约束">列级约束</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161228098.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161228098.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161228098.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161228098.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161228098.png"
        title="image-20210216161228098" /></p>
<h2 id="表级约束">表级约束</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161354577.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161354577.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161354577.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161354577.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161354577.png"
        title="image-20210216161354577" /></p>
<p>注意：主键和唯一的区别：</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161733759.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161733759.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161733759.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161733759.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216161733759.png"
        title="image-20210216161733759" /></p>
<h2 id="外键的特点">外键的特点</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216162325427.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216162325427.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216162325427.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216162325427.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216162325427.png"
        title="image-20210216162325427" /></p>
<h1 id="tcl">TCL</h1>
<h2 id="事务">事务</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216171929883.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216171929883.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216171929883.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216171929883.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216171929883.png"
        title="image-20210216171929883" /></p>
<h2 id="存储引擎">存储引擎</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216172116163.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216172116163.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216172116163.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216172116163.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210216172116163.png"
        title="image-20210216172116163" /></p>
<h2 id="事务的特性">事务的特性</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218130857058.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218130857058.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218130857058.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218130857058.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218130857058.png"
        title="image-20210218130857058" /></p>
<h2 id="使用步骤">使用步骤</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132021450.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132021450.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132021450.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132021450.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132021450.png"
        title="image-20210218132021450" /></p>
<h2 id="事务隔离">事务隔离</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132536819.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132536819.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132536819.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132536819.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132536819.png"
        title="image-20210218132536819" /></p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132547636.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132547636.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132547636.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132547636.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218132547636.png"
        title="image-20210218132547636" /></p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133156512.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133156512.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133156512.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133156512.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133156512.png"
        title="image-20210218133156512" /></p>
<h2 id="设置隔离级别">设置隔离级别</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133023411.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133023411.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133023411.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133023411.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133023411.png"
        title="image-20210218133023411" /></p>
<h2 id="savepoint设置回滚点">savepoint设置回滚点</h2>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133435396.png"
        data-srcset="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133435396.png, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133435396.png 1.5x, https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133435396.png 2x"
        data-sizes="auto"
        alt="https://raw.githubusercontent.com/yzuxqz/pic-bed/master/notes-img/image-20210218133435396.png"
        title="image-20210218133435396" /></p>
<p>25号删了，28号未删除</p>
</div><div class="post-footer" id="post-footer">
    <div class="post-info">
        <div class="post-info-line">
            <div class="post-info-mod">
                <span>更新于 2021-06-24</span>
            </div>
            <div class="post-info-license"></div>
        </div>
        <div class="post-info-line">
            <div class="post-info-md"><span>
                            <a class="link-to-markdown" href="/posts/mysql/index.md" target="_blank">阅读原始文档</a>
                        </span></div>
            <div class="post-info-share">
                <span><a href="javascript:void(0);" title="分享到 Twitter" data-sharer="twitter" data-url="http://example.org/posts/mysql/" data-title="mysql" data-via="xxxx" data-hashtags="mysql,数据库"><i class="fab fa-twitter fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Facebook" data-sharer="facebook" data-url="http://example.org/posts/mysql/" data-hashtag="mysql"><i class="fab fa-facebook-square fa-fw"></i></a><a href="javascript:void(0);" title="分享到 WhatsApp" data-sharer="whatsapp" data-url="http://example.org/posts/mysql/" data-title="mysql" data-web><i class="fab fa-whatsapp fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Line" data-sharer="line" data-url="http://example.org/posts/mysql/" data-title="mysql"><i data-svg-src="/lib/simple-icons/icons/line.min.svg"></i></a><a href="javascript:void(0);" title="分享到 微博" data-sharer="weibo" data-url="http://example.org/posts/mysql/" data-title="mysql"><i class="fab fa-weibo fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Myspace" data-sharer="myspace" data-url="http://example.org/posts/mysql/" data-title="mysql" data-description=""><i data-svg-src="/lib/simple-icons/icons/myspace.min.svg"></i></a><a href="javascript:void(0);" title="分享到 Blogger" data-sharer="blogger" data-url="http://example.org/posts/mysql/" data-title="mysql" data-description=""><i class="fab fa-blogger fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Evernote" data-sharer="evernote" data-url="http://example.org/posts/mysql/" data-title="mysql"><i class="fab fa-evernote fa-fw"></i></a></span>
            </div>
        </div>
    </div>

    <div class="post-info-more">
        <section class="post-tags"><i class="fas fa-tags fa-fw"></i>&nbsp;<a href="/tags/mysql/">mysql</a>,&nbsp;<a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a></section>
        <section>
            <span><a href="javascript:void(0);" onclick="window.history.back();">返回</a></span>&nbsp;|&nbsp;<span><a href="/">主页</a></span>
        </section>
    </div>

    <div class="post-nav"><a href="/posts/java/" class="prev" rel="prev" title="java"><i class="fas fa-angle-left fa-fw"></i>java</a>
            <a href="/posts/springmvc/" class="next" rel="next" title="SpringMVC">SpringMVC<i class="fas fa-angle-right fa-fw"></i></a></div>
</div>
<div id="comments"></div></article></div>
            </main><footer class="footer">
        <div class="footer-container"><div class="footer-line">由 <a href="https://gohugo.io/" target="_blank" rel="noopener noreffer" title="Hugo 0.89.1">Hugo</a> 强力驱动 | 主题 - <a href="https://github.com/dillonzq/LoveIt" target="_blank" rel="noopener noreffer" title="LoveIt 0.2.10"><i class="far fa-kiss-wink-heart fa-fw"></i> LoveIt</a>
                </div><div class="footer-line"><i class="far fa-copyright fa-fw"></i><span itemprop="copyrightYear">2021</span><span class="author" itemprop="copyrightHolder">&nbsp;<a href="https://github.com/yzuxqz" target="_blank">yzuxqz</a></span></div>
        </div>
    </footer></div>

        <div id="fixed-buttons"><a href="#" id="back-to-top" class="fixed-button" title="回到顶部">
                <i class="fas fa-arrow-up fa-fw"></i>
            </a><a href="#" id="view-comments" class="fixed-button" title="查看评论">
                <i class="fas fa-comment fa-fw"></i>
            </a>
        </div><link rel="stylesheet" href="/lib/katex/katex.min.css"><link rel="stylesheet" href="/lib/katex/copy-tex.min.css"><script type="text/javascript" src="/lib/smooth-scroll/smooth-scroll.min.js"></script><script type="text/javascript" src="/lib/lazysizes/lazysizes.min.js"></script><script type="text/javascript" src="/lib/clipboard/clipboard.min.js"></script><script type="text/javascript" src="/lib/sharer/sharer.min.js"></script><script type="text/javascript" src="/lib/katex/katex.min.js"></script><script type="text/javascript" src="/lib/katex/auto-render.min.js"></script><script type="text/javascript" src="/lib/katex/copy-tex.min.js"></script><script type="text/javascript" src="/lib/katex/mhchem.min.js"></script><script type="text/javascript">window.config={"code":{"copyTitle":"复制到剪贴板","maxShownLines":10},"comment":{},"math":{"delimiters":[{"display":true,"left":"$$","right":"$$"},{"display":true,"left":"\\[","right":"\\]"},{"display":false,"left":"$","right":"$"},{"display":false,"left":"\\(","right":"\\)"}],"strict":false}};</script><script type="text/javascript" src="/js/theme.min.js"></script></body>
</html>
